package com.code.lightning.code.mysql;

import com.code.lightning.code.base.BasicDBConnect;
import com.code.lightning.common.api.BasicDataBaseInfoAnalyse;
import com.code.lightning.common.domain.BasicDataBaseMetaData;
import com.code.lightning.common.domain.BasicTableFieldMetaData;
import com.code.lightning.common.domain.BasicTableMetaData;
import com.code.lightning.common.domain.FieldReference;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * MySQL数据库基础元数据获取
 * @author LGX_TvT
 * @date 2021-01-15 16:30
 */
@Slf4j
public class MySqlInfoAnalyse implements BasicDataBaseInfoAnalyse {

    private Connection connection;

    public MySqlInfoAnalyse() {
    }

    public MySqlInfoAnalyse(Connection connection) {
        this.connection = connection;
    }

    public Connection getConnection() {
        return this.connection;
    }

    public void setConnection() {
        this.connection = connection;
    }

    @Override
    public BasicDataBaseMetaData getDataBaseMetaData(String dbName, Connection connection) {
        if (this.connection == null) {
            log.error("DataBase Connect is null.");
            return null;
        }
        String sql = String.format("SELECT * FROM INFORMATION_SCHEMA.`SCHEMATA` WHERE SCHEMA_NAME = '%s'", dbName);
        BasicDataBaseMetaData basicDataBaseMetaData = new BasicDataBaseMetaData();
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);
            resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                basicDataBaseMetaData.setDbCategory(connection.getMetaData().getDatabaseProductName().toUpperCase());
                basicDataBaseMetaData.setSchemaName(resultSet.getString("SCHEMA_NAME"));
                basicDataBaseMetaData.setDefaultCharacterSetName(resultSet.getString("DEFAULT_CHARACTER_SET_NAME"));
                basicDataBaseMetaData.setDefaultCollationName(resultSet.getString("DEFAULT_COLLATION_NAME"));
            }
            log.info("GET DataBaseMetaData SUCCESS: {}", basicDataBaseMetaData);
            return basicDataBaseMetaData;
        } catch (SQLException throwables) {
            log.error("GetDataBaseMetaData Error: dbName={}", throwables.getMessage());
            throwables.printStackTrace();
        } finally {
            BasicDBConnect.close(null, pstmt, resultSet);
        }
        return null;
    }

    @Override
    public BasicDataBaseMetaData getDataBaseMetaData(String dbName) {
        return this.getDataBaseMetaData(dbName, this.connection);
    }

    @Override
    public List<BasicDataBaseMetaData> getDataBaseMetaData(Connection connection) {
        if (this.connection == null) {
            log.error("DataBase Connect is null.");
            return null;
        }
        String sql = "SELECT * FROM INFORMATION_SCHEMA.`SCHEMATA`";
        List<BasicDataBaseMetaData> dataBaseMetaDataList = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);
            resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                BasicDataBaseMetaData basicDataBaseMetaData = new BasicDataBaseMetaData();
                basicDataBaseMetaData.setDbCategory(connection.getMetaData().getDatabaseProductName().toUpperCase());
                basicDataBaseMetaData.setSchemaName(resultSet.getString("SCHEMA_NAME"));
                basicDataBaseMetaData.setDefaultCharacterSetName(resultSet.getString("DEFAULT_CHARACTER_SET_NAME"));
                basicDataBaseMetaData.setDefaultCollationName(resultSet.getString("DEFAULT_COLLATION_NAME"));
                dataBaseMetaDataList.add(basicDataBaseMetaData);
            }
            return dataBaseMetaDataList;
        } catch (SQLException throwables) {
            log.error("GetDataBaseMetaData Error: {}", throwables.getMessage());
            throwables.printStackTrace();
        } finally {
            BasicDBConnect.close(null, pstmt, resultSet);
        }
        return null;
    }

    @Override
    public List<BasicDataBaseMetaData> getDataBaseMetaData() {
        return this.getDataBaseMetaData(this.connection);
    }

    @Override
    public List<BasicTableMetaData> getTableMetaData(String dbName, Connection connection) {
        if (this.connection == null) {
            log.error("DataBase Connect is null.");
            return null;
        }
        List<BasicTableMetaData> basicTableMetaDataList = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            String sql = String.format("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%s'", dbName);
            pstmt = connection.prepareStatement(sql);
            resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                BasicTableMetaData basicTableMetaData = new BasicTableMetaData();
                basicTableMetaData.setTableSchema(resultSet.getString("TABLE_SCHEMA"));
                basicTableMetaData.setTableName(resultSet.getString("TABLE_NAME"));
                basicTableMetaData.setTableType(resultSet.getString("TABLE_TYPE"));
                basicTableMetaData.setEngine(resultSet.getString("ENGINE"));
                basicTableMetaData.setVersion(resultSet.getString("VERSION"));
                basicTableMetaData.setAutoIncrement(resultSet.getString("AUTO_INCREMENT"));
                basicTableMetaData.setCreateTime(resultSet.getString("CREATE_TIME"));
                basicTableMetaData.setUpdateTime(resultSet.getString("UPDATE_TIME"));
                basicTableMetaData.setCheckTime(resultSet.getString("CHECK_TIME"));
                basicTableMetaData.setTableCollation(resultSet.getString("TABLE_COLLATION"));
                basicTableMetaData.setTableComment(resultSet.getString("TABLE_COMMENT"));
                basicTableMetaDataList.add(basicTableMetaData);
            }
            return basicTableMetaDataList;
        } catch (Exception e) {
            log.error("GetTableMetaData Error: {}", e.getMessage());
            e.printStackTrace();
        } finally {
            BasicDBConnect.close(null, pstmt, resultSet);
        }
        return null;
    }

    @Override
    public List<BasicTableMetaData> getTableMetaData(String dbName) {
        return this.getTableMetaData(dbName, this.connection);
    }

    @Override
    public BasicTableMetaData getTableMetaData(String dbName, String tbName, Connection connection) {
        if (this.connection == null) {
            log.error("DataBase Connect is null.");
            return null;
        }
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        BasicTableMetaData basicTableMetaData = new BasicTableMetaData();
        try {
            String sql = String.format("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'", dbName, tbName);
            pstmt = connection.prepareStatement(sql);
            resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                basicTableMetaData.setTableSchema(resultSet.getString("TABLE_SCHEMA"));
                basicTableMetaData.setTableName(resultSet.getString("TABLE_NAME"));
                basicTableMetaData.setTableType(resultSet.getString("TABLE_TYPE"));
                basicTableMetaData.setEngine(resultSet.getString("ENGINE"));
                basicTableMetaData.setVersion(resultSet.getString("VERSION"));
                basicTableMetaData.setAutoIncrement(resultSet.getString("AUTO_INCREMENT"));
                basicTableMetaData.setCreateTime(resultSet.getString("CREATE_TIME"));
                basicTableMetaData.setUpdateTime(resultSet.getString("UPDATE_TIME"));
                basicTableMetaData.setCheckTime(resultSet.getString("CHECK_TIME"));
                basicTableMetaData.setTableCollation(resultSet.getString("TABLE_COLLATION"));
                basicTableMetaData.setTableComment(resultSet.getString("TABLE_COMMENT"));
            }
            return basicTableMetaData;
        } catch (Exception e) {
            log.error("GetTableMetaData Error: {}", e.getMessage());
            e.printStackTrace();
        } finally {
            BasicDBConnect.close(null, pstmt, resultSet);
        }
        return null;
    }

    @Override
    public BasicTableMetaData getTableMetaData(String dbName, String tbName) {
        return this.getTableMetaData(dbName, tbName, this.connection);
    }

    @Override
    public List<BasicTableFieldMetaData> getTableFieldMetaData(String dbName, String tbName, Connection connection) {
        if (this.connection == null) {
            log.error("DataBase Connect is null.");
            return null;
        }
        List<BasicTableFieldMetaData> dataBaseColunmsList = new ArrayList<>();
        String sql = String.format("SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'", dbName, tbName);
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);
            resultSet = pstmt.executeQuery();
            while (resultSet.next()) {
                BasicTableFieldMetaData dataBaseColunms = new BasicTableFieldMetaData();
                dataBaseColunms.setTableSchema(resultSet.getString("TABLE_SCHEMA"));
                dataBaseColunms.setTableName(resultSet.getString("TABLE_NAME"));
                dataBaseColunms.setColumnName(resultSet.getString("COLUMN_NAME"));
                dataBaseColunms.setOrdinalPosition(resultSet.getString("ORDINAL_POSITION"));
                dataBaseColunms.setColumnDefault(resultSet.getString("COLUMN_DEFAULT"));
                dataBaseColunms.setIsNullable(resultSet.getString("IS_NULLABLE"));
                dataBaseColunms.setDataType(resultSet.getString("DATA_TYPE"));
                dataBaseColunms.setCharacterMaximumLength(resultSet.getString("CHARACTER_MAXIMUM_LENGTH"));
                dataBaseColunms.setCharacterOctetLength(resultSet.getString("CHARACTER_OCTET_LENGTH"));
                dataBaseColunms.setColumnType(resultSet.getString("COLUMN_TYPE"));
                dataBaseColunms.setColumnKey(resultSet.getString("COLUMN_KEY"));
                dataBaseColunms.setExtra(resultSet.getString("EXTRA"));
                dataBaseColunms.setPrivileges(resultSet.getString("PRIVILEGES"));
                dataBaseColunms.setColumnComment(resultSet.getString("COLUMN_COMMENT"));

                // 获取主外键
                String fkSql = String.format("SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = '%s' AND TABLE_NAME='%s' AND COLUMN_NAME = '%s'", dataBaseColunms.getTableSchema(), dataBaseColunms.getTableName(), dataBaseColunms.getColumnName());
                PreparedStatement fkpstmt = connection.prepareStatement(fkSql);
                ResultSet fkResultSet = fkpstmt.executeQuery();
                while (fkResultSet.next()) {
                    FieldReference fieldReference = new FieldReference();
                    fieldReference.setConstraintName(fkResultSet.getString("CONSTRAINT_SCHEMA"));
                    fieldReference.setTableSchema(fkResultSet.getString("TABLE_CATALOG"));
                    fieldReference.setTableName(fkResultSet.getString("TABLE_SCHEMA"));
                    fieldReference.setColumnName(fkResultSet.getString("COLUMN_NAME"));
                    fieldReference.setReferencesTableSchema(fkResultSet.getString("REFERENCED_TABLE_SCHEMA"));
                    fieldReference.setReferenceTableName(fkResultSet.getString("REFERENCED_TABLE_NAME"));
                    fieldReference.setReferenceColumnName(fkResultSet.getString("REFERENCED_COLUMN_NAME"));
                    dataBaseColunms.setReference(fieldReference);
                }
                dataBaseColunmsList.add(dataBaseColunms);
                BasicDBConnect.close(null, fkpstmt, fkResultSet);
            }
            return dataBaseColunmsList;
        } catch (SQLException throwables) {
            log.error("GetTableFieldMetaData Error: {}", throwables.getMessage());
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<BasicTableFieldMetaData> getTableFieldMetaData(String dbName, String tbName) {
        return this.getTableFieldMetaData(dbName, tbName, this.connection);
    }

    /**
     * 关闭数据库连接
     * @param connection 数据库连接
     */
    public void close(Connection connection) {
         BasicDBConnect.close(this.connection, null, null);
    }

    /**
     * 关闭数据库连接
     */
    public void close() {
        this.close(this.connection);
    }

}
